DEFINE VARIABLE tbl AS CHARACTER.
DEFINE VARIABLE ptbl AS CHARACTER.
DEFINE VARIABLE ssql1 AS CHARACTER FORMAT 'x(4000)' .
DEFINE VARIABLE ssql2 AS CHARACTER FORMAT 'x(4000)' EXTENT 10.
DEFINE VARIABLE i AS INT.
DEFINE VARIABLE ii AS INT.

ii = 0.
OUTPUT TO d:\hk_sync.p .

/*tbl ='si_mstr,loc_mstr,um_mstr,cp_mstr,vp_mstr,cm_mstr,vd_mstr,sp_mstr,emp_mstr,ct_mstr,ad_mstr'.*/
/*tbl ='so_mstr,sod_det,po_mstr,pod_det,wo_mstr,wod_det'.*/
tbl ='code_mstr,exr_rate,si_mstr,loc_mstr,pl_mstr,cp_mstr,um_mstr,sp_mstr,emp_mstr,ct_mstr'.

/*
PUT UNFORMATTED 'DEFINE VAR mintrnbr LIKE dth_trnbr.' SKIP.
PUT UNFORMATTED 'DEFINE VAR maxtrnbr LIKE dth_trnbr.' SKIP.

PUT UNFORMATTED 'FIND LAST dth_hist NO-LOCK NO-ERROR.' SKIP.
PUT UNFORMATTED 'maxtrnbr = IF AVAIL(dth_hist) THEN dth_trnbr ELSE 1.' SKIP.



PUT UNFORMATTED "FIND FIRST CODE_mstr WHERE code_fldname ='dth_hist_maxid' NO-ERROR." SKIP.
PUT UNFORMATTED "IF AVAIL(CODE_mstr) THEN DO: " SKIP.
PUT UNFORMATTED "    mintrnbr = int(code_value)." SKIP.
PUT UNFORMATTED "    code_value = STRING(maxtrnbr)." SKIP.

PUT UNFORMATTED "END." SKIP.
PUT UNFORMATTED "ELSE DO:" SKIP.
PUT UNFORMATTED "    CREATE CODE_mstr." SKIP.
PUT UNFORMATTED "    ASSIGN code_fldname ='dth_hist_maxid'" SKIP.
PUT UNFORMATTED "           code_value = STRING(maxtrnbr)." SKIP.
PUT UNFORMATTED "    mintrnbr = 1." SKIP.
PUT UNFORMATTED "END." SKIP.

*/


PUT UNFORMATTED 'DEFINE VAR i AS INT. 'skip.
PUT UNFORMATTED "DEFINE VARIABLE ssql AS CHARACTER FORMAT 'x(4000)' EXTENT 10." skip.
PUT UNFORMATTED 'OUTPUT TO d:\hk_sync_data.txt. 'skip.
PUT UNFORMATTED 'i = 1. 'skip.

FOR EACH _file NO-LOCK WHERE index(tbl,_file-name ) > 0 :
    ptbl= ENTRY ( 1 , _file-name ,'_' ) + '_'.  
    ssql1 = ''.
    ssql2 = ''.
    ii = 1.
    ssql1 =  "insert into " + _file-name + "(" + ptbl + "recid," + Ptbl + "HKDB".
    ssql2[ii] =  ") values("" + STRING(RECID(" +  _file-name  + ")) + "",'HK'".
    
        FOR EACH _field OF _file NO-LOCK    BY _order:
             IF LENGTH(ssql2[ii]) > 2500 THEN ii = ii + 1.
              IF _extent = 0 THEN DO:
                 ssql1 = ssql1 +  "," +  _field-name .
    
    
                  IF _Initial = '?' OR _Initial = ? THEN DO:
                     IF  index("character,byte", _data-type) > 0   THEN DO:
                          IF index(_label,'Desc') > 0 OR index(_label,'Remarks') > 0 OR index(_label,'Comments') > 0 THEN DO:
                                ssql2[ii] = ssql2[ii] +  ",'""  +  (if " + _field-name + " = ? then """" else  REPLACE(REPLACE(REPLACE(REPLACE(" +  _field-name  + ","","",""""),""'"",""""),chr(10),""""),chr(13),"""")) + ""'".
                          END.
                          ELSE DO:
                                ssql2[ii] = ssql2[ii] +  ",'""  +  (if " + _field-name + " = ? then """" else  " +  _field-name  +  ") + ""'".
                          END.
                          
                        
                     END.
                     ELSE IF index("logical", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ","" + string(int(if " +  _field-name  + " = ? then no else " + _field-name + ")) + """.
                     END.
                     ELSE IF index("bigint,decimal,double,float,integer,short", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ","" + (if " + _field-nam + " = ? then '0' else string(" +  _field-name  + ")) + """.
                     END.
                     ELSE IF index("date,time", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ",'"" + (if " + _field-nam + " = ? then '1900/01/01' else string(" +  _field-name  + ")) + ""'".
                     END.
                  END.
                  ELSE DO:
                     IF  index("character,byte", _data-type) > 0   THEN DO:
                         IF index(_label,'Desc') > 0 OR index(_label,'Remarks') > 0 OR index(_label,'Comments') > 0 THEN DO:
                            ssql2[ii] = ssql2[ii] +  ",'""  +     REPLACE(REPLACE(REPLACE(REPLACE(" +  _field-name  + ","","",""""),""'"",""""),chr(10),""""),chr(13),"""")  + ""'".
                         END.
                         ELSE DO:
                            ssql2[ii] = ssql2[ii] +  ",'""  +  " +  _field-name + "  + ""'".
                         END.
                        
                     END.
                     ELSE IF index("logical", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ","" + string(int(" + _field-name + ")) + """.
                     END.
                     ELSE IF index("bigint,decimal,double,float,integer,short", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ","" +  string(" +  _field-name  + ") + """.
                     END.
                     ELSE IF index("date,time", _data-type) > 0   THEN DO:
                          ssql2[ii] = ssql2[ii] +  ",'"" +   string(" +  _field-name  + ") + ""'".
                     END.
                  END.
             END.
             ELSE   DO:
                 DO i = 1 to _extent :
                     IF LENGTH(ssql2[ii]) > 2500 THEN ii = ii + 1.
                     ssql1 = ssql1 +  "," +  _field-name + "__" + STRING(i) .
        
                     IF   _Initial = '?' OR _Initial = ? THEN DO:
                         IF  index("character,byte", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ",'"" + (if " + _field-name  + "[" + STRING(i) + "] = ? then """" else REPLACE(REPLACE(" +  _field-name  + "[" + STRING(i) + "],"","",""""),""'"","""")) + ""'".
        
                         END.
                         ELSE IF index("logical", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ","" + string(int(if " +  _field-name   + "[" + STRING(i) + "] = ? then no else " +  _field-name   + "[" + STRING(i) + "])) + """.
                         END.
                         ELSE IF index("bigint,decimal,double,float,integer,short,logical", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ","" + (if " + _field-nam + "[" + STRING(i) + "] = ? then '0' else string(" +  _field-name   + "[" + STRING(i) + "])) + """.
                         END.
                         ELSE IF index("date,time", _data-type) > 0   THEN do:
                              ssql2[ii] = ssql2[ii] +  ",'"" + (if " + _field-nam + "[" + STRING(i) + "] = ? then '1900/01/01' else string(" +  _field-name   + "[" + STRING(i) + "])) + ""'".
                         END.
                     END.
                     ELSE DO:
                         IF  index("character,byte", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ",'"" +  REPLACE(REPLACE(" +  _field-name  + "[" + STRING(i) + "],"","",""""),""'"","""") + ""'".
        
                         END.
                         ELSE IF index("logical", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ","" + string(int( " +  _field-name   + "[" + STRING(i) + "])) + """.
                         END.
                         ELSE IF index("bigint,decimal,double,float,integer,short,logical", _data-type) > 0   THEN DO:
                              ssql2[ii] = ssql2[ii] +  ","" + string(" +  _field-name   + "[" + STRING(i) + "]) + """.
                         END.
                         ELSE IF index("date,time", _data-type) > 0   THEN do:
                              ssql2[ii] = ssql2[ii] +  ",'"" +  string(" +  _field-name   + "[" + STRING(i) + "]) + ""'".
                         END.
                     END.
                  END.
    
                
             END. /* F _extent = 0 */
        
        
        END. /* FOR EACH _field */
    ssql2[ii] = ssql2[ii] + ")". 
   
    
    PUT UNFORMATTED  "FOR EACH " + _file-name + " NO-LOCK :" SKIP.

    PUT UNFORMATTED " EXPORT  string(i) ""delete from " + _file-name + " where " + Ptbl + "HKDB='HK' and " + ptbl + "recid="" + STRING(RECID(" +  _file-name  + ")) skip." SKIP.
    
 PUT UNFORMATTED ' i =i + 1. 'skip.
 

 
      
        PUT UNFORMATTED  " ssql = ''." skip.
        PUT UNFORMATTED  " ssql[1] = """ ssql1 """."  SKIP.
    
    DO i = 1 TO 9:
        PUT UNFORMATTED  " ssql[" + STRING(i + 1) + "] ="""   ssql2[i]  """."  SKIP.
    END.

    /* PUT UNFORMATTED  "EXPORT string(i) """ ssql2[i]  """."  SKIP.*/


    PUT UNFORMATTED " EXPORT string(i) ssql[1] + ssql[2] + ssql[3] + ssql[4] + ssql[5] + ssql[6] + ssql[7] + ssql[8]  + ssql[9] + ssql[10] SKIP." SKIP.
    PUT UNFORMATTED ' i =i + 1. 'skip.
    PUT UNFORMATTED "end. "   SKIP.
   
END.

 PUT UNFORMATTED "OUTPUT CLOSE.". 
OUTPUT CLOSE.

 
